global.ReadableStream = require('stream').Readable;
const express = require('express')
const cors = require('cors')
const mysql = require('mysql2')
const crypto = require('crypto')
const cheerio = require('cheerio')
const multer = require('multer');
const formidable = require('formidable')
const path = require('path')
const fs = require('fs')
const tool = require('./tool')
const xlsx = require('xlsx')
const http = require('http')
const axios = require('axios')
const QRCode = require('qrcode')
const bodyParser = require('body-parser');
const WXPAY = require('./wxpay');

const connectionConfig = {
    host: '127.0.0.1',
    user: 'root',
    password: 'Catiouszty2004_',
    database: 'coffee_shop',
    port: 3306,
    charset: 'utf8mb4'
}

const config = {
    port: 1005,
    prefix: '/api'
}

const connection = mysql.createConnection(connectionConfig);

console.clear()

const runner = {
    tasks: [],
    running: false,
    async run() {
        if (this.tasks.length == 0) return
        if (this.running) return
        this.running = true
        console.log('运行')
        while (1) {
            if (this.tasks.length == 0) {
                this.running = false
                console.log('停止')
                break
            }
            let curr = this.tasks[0]
            try {
                let re = await curr.func(curr.data, curr.headers)
                curr.res.send({
                    status: 1,
                    content: 'success',
                    data: re
                })
            } catch (error) {
                console.log(error)
                try {
                    curr.res.send({
                        status: 0,
                        content: tool.string.delete.bySubString(error.toString(), 'Error:')
                    })
                } catch (error) {

                }
            }
            this.shift()
        }
    },
    async add(func, res, headers, data) {
        this.tasks.push({ func, res, headers, data })
        this.run()
        await tool.wait(0.1)
        this.run()
    },
    shift() {
        this.tasks.shift()
    }
}

//连接数据库
function connectDatabase() {
    return new Promise((resolve, reject) => {
        connection.connect(err => {
            return resolve(err == null ? 'OK' : err.toString())
        })
    })
}

const getInsertIntoSQLString = (string) => {
    string = string.split('')
    let re = ''
    for (let i = 0; i < string.length; i++) {
        if (string[i] == "'") string[i] = '`'
        re += string[i]
    }
    return re
}
//执行SQL
function execSQL(SQL) {
    return new Promise((resolve, reject) => {
        connection.query(SQL, (error, results, fields) => {
            if (error) {
                console.log(error)
                // connection.query(`insert into log_sql(\`sql\`, status, results, time) values('${getInsertIntoSQLString(SQL)}',0,${error.toString()},'${(new Date()).toString()}')`)
                return reject()
            }
            // connection.query(`insert into log_sql(\`sql\`, status, results, time) values('${getInsertIntoSQLString(SQL)}',1,${JSON.stringify(results)},'${(new Date()).toString()}')`, (error1, results1, field1) => {
            //     console.log(error1)
            // })
            return resolve(results)
        })
    })
}

//抛出错误
function throwNewError(error) {
    throw new Error(error)
}

//数据库方法
const dataBaseMethods = {
    async exe(sql) {
        console.log(sql)
        return await execSQL(sql)
    },
    async getTableColumns(tableName) {
        try {
            return await tool.array.format(await execSQL(`describe \`${tableName}\`;`), (e) => {
                return {
                    name: e.Field,
                    type: e.Type
                }
            })
        } catch (error) {
            throwNewError(`不存在表${tableName}`)
        }
    },
    async genNewID(tableName) {
        let re = await execSQL(`select id from \`${tableName}\` order by id asc`)
        let index = 1
        while (1) {
            if (re[index - 1] == undefined) return index
            if (re[index - 1].id != index) return index
            index++
        }
    },
    async insertInto(tableName, values = {}) {
        let re = await this.getTableColumns(tableName)
        let sql = `insert into \`${tableName}\` values(`
        values.id = await this.genNewID(tableName)
        values.createdAt = (new Date()).toString()
        values.updatedAt = (new Date()).toString()
        for (let i = 0; i < re.length; i++) {
            let value
            if (values[re[i].name] == undefined) {
                value = (re[i].type.indexOf('int') != -1) ? 0 : ''
            } else {
                value = values[re[i].name]
            }
            if (!((typeof (value) == 'number' && re[i].type.indexOf('int') != -1) || (typeof (value) == 'string' && re[i].type == 'longtext'))) {
                throwNewError(`${re[i].name}的值的类型应为${re[i].type}，而不是${typeof (value)}`)
            }
            if (i) sql += ','
            if (typeof (value) == 'number') {
                sql += value.toString()
            } else {
                sql += "'" + value.toString() + "'"
            }
        }
        sql += ');'
        await this.exe(sql)
        return values.id
    },
    async deleteById(tableName, id) {
        return await this.exe(`delete from \`${tableName}\` where id = ${id}`)
    },
    async updateById(tableName, id, values = {}) {
        let re = await this.getTableColumns(tableName)
        let sql = `update ${tableName} set `
        delete values.id
        delete values.createdAt
        values.updatedAt = (new Date()).toString()
        let c = 0
        for (let i = 0; i < re.length; i++) {
            if (values[re[i].name] == undefined) continue
            let value = values[re[i].name]
            if (!((typeof (value) == 'number' && re[i].type.indexOf('int') != -1) || (typeof (value) == 'string' && re[i].type == 'longtext'))) {
                throwNewError(`${re[i].name}的值的类型应为${re[i].type}，而不是${typeof (value)}`)
            }
            if (c) sql += ', '
            sql += "`" + re[i].name + "` = "
            if (re[i].type.indexOf('int') != -1) {
                sql += value.toString()
            } else {
                sql += "'" + value.toString() + "'"
            }
            c++
        }
        sql += ` where id = ${id};`
        return await this.exe(sql)
    },
    async select(sql, format = {}) {
        if (sql.slice(0, 6) != 'select') throwNewError('SQL语句不是select语句')
        let re = await this.exe(sql)
        format.createdAt = 'date'
        format.updatedAt = 'date'
        return tool.array.format(re, async (item) => {
            let t = await tool.object.for(item, (k, v) => {
                if (item[k] != undefined) {
                    if (format[k] == 'date') {
                        return new Date(item[k])
                    }
                    if (format[k] == 'number') {
                        return parseFloat(item[k])
                    }
                    try {
                        return JSON.parse(item[k])
                    } catch (error) {

                    }
                    return item[k]
                } else {
                    return null
                }
            })
            return t
        })
    },
}

//系统设置
const settings = {
    parseValue(type, value) {
        if (type == 'string') {
            return value.toString()
        }
        if (type == 'number') {
            return parseFloat(value)
        }
        if (type == 'date') {
            return new Date(value)
        }
        if (type == 'object') {
            if (typeof (value) == 'object') return value
            return JSON.parse(value)
        }
        if (type == 'boolean') {
            return value.toString() == 'true' ? true : false
        }
        return value
    },
    async getItem(key) {
        let re = await dataBaseMethods.select("select * from settings where `key` = '" + key.toString() + "'")
        if (re.length) {
            return this.parseValue(tool.JSON.parse(re[0].value).type, tool.JSON.parse(re[0].value).value)
        } else {
            return null
        }
    },
    async getItemInfo(key) {
        let re = await dataBaseMethods.select("select * from settings where `key` = '" + key.toString() + "'")
        if (re.length) {
            let t = re[0]
            t.valueParsed = this.parseValue(tool.JSON.parse(re[0].value).type, tool.JSON.parse(re[0].value).value)
            return t
        } else {
            return undefined
        }
    },
    async setItem(key, value, explain = undefined) {
        if (!key) {
            throw new Error(`未指定key`)
        }
        let t
        if ((await this.getItem(key)) != null) {
            t = await this.getItem(key)
            await this.removeItem(key)
        }
        await dataBaseMethods.insertInto('settings', {
            key: key,
            value: JSON.stringify({
                type: typeof (value),
                value: value
            }),
            explain: explain == undefined ? t.explain : explain
        })
    },
    async removeItem(key) {
        await execSQL(`delete from settings where \`key\` = '${key}'`)
    },
    list: [
        {
            key: 'banner',
            default: [
                {
                    fileName: 'th (1).jfif'
                },
                {
                    fileName: 'th (2).jfif'
                },
                {
                    fileName: 'th (3).jfif'
                },
            ],
            explain: '轮播图'
        },
        {
            key: 'notification',
            default: [
                {
                    text: '这是一条测试通知',
                    time: '2024-11-30T06:21:26.063Z'
                },
                {
                    text: '这是一条测试警告通知',
                    time: '2024-11-30T06:21:26.063Z',
                    warning: true
                },
            ],
            explain: '通知'
        }
        // {
        //     key: 'allowRegister_businessUser',
        //     default: true,
        //     explain: '允许注册 - 企业用户'
        // },
        // {
        //     key: 'allowRegister_platformAdmin',
        //     default: true,
        //     explain: '允许注册 - 平台管理员'
        // },
        // {
        //     key: 'allowRegister_consumer',
        //     default: true,
        //     explain: '允许注册 - 消费者'
        // },
    ],
    async init() {
        let currentList = await dataBaseMethods.select(`select * from settings`)
        for (let i = 0; i < currentList.length; i++) {
            currentList[i].valueParsed = this.parseValue(tool.JSON.parse(currentList[i].value).type, tool.JSON.parse(currentList[i].value).value)
        }
        const exist = (key) => {
            for (let i = 0; i < currentList.length; i++) {
                if (currentList[i].key == key) {
                    return true
                }
            }
            return false
        }
        for (let i = 0; i < this.list.length; i++) {
            if (exist(this.list[i].key) == false) {
                await this.setItem(this.list[i].key, this.list[i].default, this.list[i].explain)
            }
        }
    }
}
//初始化系统设置
settings.init()

async function deleteFile(filename) {
    // 构建文件的完整路径
    const filePath = path.join(__dirname, 'files', filename);

    try {
        // 检查文件是否存在
        const fileExists = await new Promise((resolve, reject) => {
            fs.access(filePath, fs.constants.F_OK, (err) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(true);
                }
            });
        });

        if (fileExists) {
            // 删除文件
            await new Promise((resolve, reject) => {
                fs.unlink(filePath, (err) => {
                    if (err) {
                        reject(err);
                    } else {
                        resolve();
                    }
                });
            });
            console.log(`文件 ${filename} 已被删除`);
        } else {
            console.log(`文件 ${filename} 不存在`);
        }
    } catch (error) {
        console.error(`删除文件时出错: ${error}`);
    }
}

async function main() {
    try {
        console.clear()
        let re = await connectDatabase()
        if (re != 'OK') {
            console.log('coffee_shop server 未能正常启动，原因：' + re)
            process.exit(0)
        }
        const app = express();
        app.use(express.json())
        app.use(cors())
        const storage = multer.diskStorage({
            destination: 'files/',
            filename: function (req, file, cb) {
                const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
                const fileExtension = path.extname(file.originalname);
                cb(null, file.fieldname + '-' + uniqueSuffix + fileExtension);
            }
        })
        const upload = multer({ storage: storage })
        let parsedPorts = []
        const parseAllPorts = (o, currentName = '') => {
            tool.object.for(o, (k, v) => {
                if (v.func == undefined) {
                    parseAllPorts(v, currentName + '/' + k)
                } else {
                    let c = {
                        api: currentName + '/' + k,
                        ...v
                    }
                    parsedPorts.push(c)
                    app[c.method == 'get' ? 'get' : 'post'](config.prefix + c.api, async (req, res) => {
                        let token = req.headers.token
                        if (c.auth) {
                            try {
                                await user.checkToken(token, c.auth.vip === true, c.auth.admin === true)
                            } catch (error) {
                                res.send({
                                    status: -1,
                                    content: error.toString().split('Error: ')[1]
                                })
                                return
                            }
                        }
                        let data
                        try {
                            // data = decodeURIComponent()
                            data = req.body
                        } catch (error) {
                            console.log(error)
                        }
                        try {
                            // console.log(data)
                            if (typeof (data) !== 'object') data = {}
                            data = {
                                query: req.query,
                                param: data
                            }
                            // let re = await c.func(data, req.headers)
                            // res.send({
                            //     status: 1,
                            //     content: 'success',
                            //     data: re
                            // })
                            // 到runner中执行
                            runner.add(c.func, res, req.headers, data)
                            return
                        } catch (error) {
                            res.send({
                                status: 0,
                                content: tool.string.delete.bySubString(error.toString(), 'Error:')
                            })
                            return
                        }
                    })
                }
            })
        }
        parseAllPorts(ports)
        app.post(config.prefix + '/file/upload', upload.single('file'), async (req, res) => {
            if (!req.file) {
                res.status(400).json({ error: '上传失败' });


                // clog('上传文件', undefined, '', '失败', data)
            } else {
                res.json({
                    status: 1,
                    content: '上传成功',
                    data: req.file.filename
                })


                // clog('上传文件', undefined, req.file.filename, '成功', data)
            }
        })
        app.get(config.prefix + '/file/download/:filename', async (req, res) => {
            const filename = req.params.filename;
            if (filename.indexOf('%b64%') != -1) {
                let re = await execSQL("select data from file where name = '" + filename + "'")
                if (re.length == 0) {
                    res.send('')
                    return
                }
                re = re[0].data
                res.setHeader('Content-Type', `image/jpeg`);
                res.setHeader('Cache-Control', 'public, max-age=31536000, immutable');
                res.send(re)
                return
            }
            const filePath = path.join('files', filename);
            if (!fs.existsSync(filePath)) {
                return res.status(404).json({
                    status: 0,
                    content: '无此文件'
                });
            }
            res.setHeader('Content-Disposition', `filename=${filename}`);
            const fileStream = fs.createReadStream(filePath);
            fileStream.pipe(res);
        })
        app.use(bodyParser.json({ limit: '5mb' }));  // 对 JSON 数据的大小限制
        app.use(bodyParser.urlencoded({ limit: '5mb', extended: true }));  // 对 URL 编码的数据大小限制
        app.listen(config.port, async () => {
            console.log('\x1b[32mcoffee_shop server Started.\x1b[37m')
            console.log('port:' + config.port.toString())

            //每五分钟执行一次，避免数据库连接断开
            setInterval(() => {
                execSQL('select * from settings')
            }, 1000 * 60 * 5);
        })
    } catch (error) {
        console.log('main函数里的错误：', error)
    }
}

main()

const appInfo = {
    appId: 'wx110f4e246caa7808',
    appSecret: '6df8554da328ef703a194c37b0bf9676'
}

//优惠券方法
const coupons = {
    async getList(openId) {
        return await dataBaseMethods.select(`select * from coupons where openId = '${openId}'`)
    },
    async add(openId, discount) {
        await dataBaseMethods.insertInto('coupons', {
            openId,
            discount
        })
    },
    async delete(id) {
        await dataBaseMethods.deleteById('coupons', id)
    }
}

//普通用户方法
const user = {
    async getSingleInfobyOpenId(openId = '') {
        let re = await dataBaseMethods.select(`select * from users where openId = '${openId}'`)
        if (re[0]) {
            re[0].vipCode = tool.encodeString(re[0].id.toString())
        }
        re[0].coupons = (await coupons.getList(openId))
        re[0].couponNum = re[0].coupons.length
        if (re[0].isVIP) {
            if (new Date(re[0].vipEndTime) < new Date()) {
                let t = {
                    act: parseInt(re[0].vipStatus / 10),
                    pro: re[0].vipStatus % 10
                }
                re[0].vipRights = t
            }
        }
        if (!re[0].vipRights) {
            re[0].vipRights = {
                act: 0,
                pro: 0
            }
        }
        return re[0]
    },
    async searchUser(input = '', page = 1, size = 10, sortBy = 'username') {
        let re = await dataBaseMethods.select(`select * from users where username like '%${input}%' or phone like '%${input}%'`)
        if (sortBy == 'username') {
            re.sort((a, b) => {
                if (a.username > b.username) return 1
                if (a.username < b.username) return -1
                return 0
            })
        }
        if (sortBy == 'createdAt') {
            re.sort((a, b) => {
                return new Date(a.createdAt) - new Date(b.createdAt)
            })
        }
        if (sortBy == 'updatedAt') {
            re.sort((a, b) => {
                return new Date(a.updatedAt) - new Date(b.updatedAt)
            })
        }
        let total = (await dataBaseMethods.select(`select id from users`)).length
        let searchTotal = re.length
        return {
            total, searchTotal,
            data: tool.sliceByPageSize(re, page, size)
        }
    },
    async updateUserInfo(openId, value) {
        delete value.id
        delete value.openId
        delete value.wxId
        delete value.isAdmin
        delete value.createdAt
        delete value.updatedAt
        delete value.status
        let userId = await this.getSingleInfobyOpenId(openId)
        if (userId) {
            userId = userId.id
            await dataBaseMethods.updateById('users', userId, value)
            return
        }
        throw new Error('用户不存在')
    },
    async addUser(userInfo) {
        if (userInfo.openId === undefined) {
            throwNewError('未提供OpenId')
        }
        let re = await this.getSingleInfobyOpenId(userInfo.openId)
        if (re !== undefined) throwNewError('用户已存在，无需重复添加')
        userInfo.token = ''
        userInfo.isVIP = 0
        userInfo.isAdmin = 0
        userInfo.status = 0
        userInfo.vipStatus = 0
        userInfo.vipEndTime = ''
        await dataBaseMethods.insertInto('users', userInfo)
    },
    async updateUserToken(openId) {
        let re = await this.getSingleInfobyOpenId(openId)
        if (re === undefined) throwNewError('用户不存在')
        let re1 = tool.encodeString(JSON.stringify({
            id: re.id,
            openId: re.openId,
            time: (new Date()).getTime(),
            s: tool.getRandomString()
        }))
        await dataBaseMethods.updateById('users', re.id, {
            token: re1
        })
        return re1
    },
    async checkToken(token, needVIP = false, needAdmin = false) {
        let tokenc = token
        try {
            token = JSON.parse(tool.decodeString(token))
        } catch (error) {
            throwNewError('未登录')
        }
        let re = await this.getSingleInfobyOpenId(token.openId)
        if (re === undefined) throwNewError('此操作需要用户登录')
        console.log(re.token, tokenc)
        if (re.token !== tokenc) throwNewError('已在其他设备登录')
        if ((new Date() - new Date(token.time)) > 1000 * 60 * 60 * 24 * 1) throwNewError('Token已过期')
        if (re.isVIP === 0 && needVIP) {
            throwNewError('此操作需要付费会员权限')
        }
        if (re.isVIP === 1 && needVIP) {
            if (new Date() > new Date(re.vipEndTime)) {
                throwNewError('付费会员已过期')
                await dataBaseMethods.updateById(re.id, {
                    isVIP: 0,
                })
            }
        }
        if (re.isAdmin === 0 && needAdmin) {
            throwNewError('此操作需要管理员权限')
        }
        if (re.status === 1) {
            throwNewError('您的账号已被冻结')
        }
        return true
    },
    async getOpenId(code) {
        let re1 = await axios.get(`https://api.weixin.qq.com/sns/jscode2session?appid=${appInfo.appId}&secret=${appInfo.appSecret}&js_code=${code}&grant_type=authorization_code`)
        return re1.data.openid
    }
}

//商品方法
const product = {
    formatProduct(item) {
        if (!item.imageUrl) item.imageUrl = []
        if (!item.options) item.options = []
        try {
            item.createdAt = new Date(item.createdAt)
        } catch (error) {
            delete item.createdAt
        }
        try {
            item.updatedAt = new Date(item.updatedAt)
        } catch (error) {
            delete item.updatedAt
        }
        try {
            item.priceDisplay = (item.price / 100).toString() + ((item.price / 100).toString().split('.')[1] ? ((item.price / 100).toString().split('.')[1].length == 1 ? '0' : '') : '')
        } catch (error) {
            delete item.updatedAt
        }
        if (item.createdAt) {
            item.createdAt_read = tool.formatTimeNew(new Date(item.createdAt))
        } else {
            item.createdAt_read = ''
        }
        if (item.updatedAt) {
            item.updatedAt_read = tool.formatTimeNew(new Date(item.updatedAt))
        } else {
            item.updatedAt_read = ''
        }
        {
            let re = ''
            for (let i = 0; i < item.options.length; i++)re += item.options[i].title + '; '
            item.optionsPreview = re
        }
        {
            let re = ''
            if (item.isVipAvailable) re += 'PLUS会员 '
            if (item.isCardAvailable) re += '优惠券 '
            if (!re) re = '无'
            item.discountPreview = re
        }
        return item
    },
    async getProductList(input, showNotAvailable = false) {
        let re = await dataBaseMethods.select(`select id, title, subTitle , imageUrl, count, price, category1, category2, isAvailable, updatedAt from products where title like '%${input}%' ${!showNotAvailable ? 'and isAvailable = 1' : ''}`)
        for (let i = 0; i < re.length; i++) {
            re[i] = this.formatProduct(re[i])
            if (re[i].imageUrl.length) {
                re[i].imageUrl = [re[i].imageUrl[0]]
            } else {
                delete re[i].imageUrl
            }
        }
        re.sort((a, b) => {
            return new Date(a.createdAt) - new Date(b.createdAt)
        })
        re.sort((a, b) => {
            return a.soldOut - b.soldOut
        })
        let cate1 = []
        for (let i = 0; i < re.length; i++) {
            cate1.push(re[i].category1)
        }
        cate1 = [...new Set(cate1)]
        for (let i = 0; i < cate1.length; i++) {
            let currentCate1Name = cate1[i]
            let cate2 = []
            for (let i1 = 0; i1 < re.length; i1++) {
                if (re[i1].category1 == currentCate1Name) {
                    cate2.push(re[i1].category2)
                }
            }
            cate2 = [...new Set(cate2)]
            for (let i1 = 0; i1 < cate2.length; i1++) {
                let currentCate2Name = cate2[i1]
                let pros = []
                for (let i2 = 0; i2 < re.length; i2++) {
                    if (re[i2].category1 == currentCate1Name && re[i2].category2 == currentCate2Name) {
                        pros.push(re[i2])
                    }
                }
                cate2[i1] = {
                    title: currentCate2Name,
                    children: JSON.parse(JSON.stringify(pros))
                }
            }
            cate1[i] = {
                title: currentCate1Name,
                children: JSON.parse(JSON.stringify(cate2))
            }
        }
        return JSON.parse(JSON.stringify(cate1))
    },
    async getOneProductInfo(id) {
        let re = await dataBaseMethods.select(`select * from products where id = ${id}`)
        if (re.length) {
            return this.formatProduct(re[0])
        } else {
            return undefined
        }
    },
    async updateInfo(item) {
        let id = item.id
        delete item.id
        delete item.createdAt
        delete item.updatedAt
        await dataBaseMethods.updateById('products', id, item)
    },
    async add(item) {
        await dataBaseMethods.insertInto('products', item)
    }
}

//活动方法
const activity = {
    async formatActivity(item) {
        if (!item.imageUrl) item.imageUrl = []
        try {
            item.startRegistration = new Date(item.startRegistration)
        } catch (error) {
            delete item.startRegistration
        }
        try {
            item.endRegistration = new Date(item.endRegistration)
        } catch (error) {
            delete item.endRegistration
        }
        try {
            item.startTime = new Date(item.startTime)
        } catch (error) {
            delete item.startTime
        }
        try {
            item.endTime = new Date(item.endTime)
        } catch (error) {
            delete item.endTime
        }
        try {
            item.createdAt = new Date(item.createdAt)
        } catch (error) {
            delete item.createdAt
        }
        try {
            item.updatedAt = new Date(item.updatedAt)
        } catch (error) {
            delete item.updatedAt
        }
        try {
            item.joinedUserCount = (await this.getJoinUserList(item.id)).length
        } catch (error) {
            item.joinedUserCount = 0
        }
        try {
            item.priceDisplay = (item.price / 100).toString() + ((item.price / 100).toString().split('.')[1] ? ((item.price / 100).toString().split('.')[1].length == 1 ? '0' : '') : '')
        } catch (error) {

        }
        if (item.createdAt) {
            item.createdAt_read = tool.formatTimeNew(new Date(item.createdAt))
        } else {
            item.createdAt_read = ''
        }
        if (item.updatedAt) {
            item.updatedAt_read = tool.formatTimeNew(new Date(item.updatedAt))
        } else {
            item.updatedAt_read = ''
        }
        if (item.endRegistration) {
            item.endRegistration_read = tool.formatTimeNew(new Date(item.endRegistration))
        } else {
            item.endRegistration_read = ''
        }
        if (item.startRegistration) {
            item.startRegistration_read = tool.formatTimeNew(new Date(item.startRegistration))
        } else {
            item.startRegistration_read = ''
        }
        if (item.startTime) {
            item.startTime_read = tool.formatTimeNew(new Date(item.startTime))
        } else {
            item.startTime_read = ''
        }
        if (item.endTime) {
            item.endTime_read = tool.formatTimeNew(new Date(item.endTime))
        } else {
            item.endTime_read = ''
        }

        {
            let ct = new Date()
            item.status = 5
            if (ct < item.endTime) item.status = 4
            if (ct < item.startTime) item.status = 3
            if (ct < item.endRegistration) item.status = 2
            if (ct < item.startRegistration) item.status = 1
        }
        return item
    },
    async getJoinUserList(id) {
        return (await dataBaseMethods.select(`select * from userJoinActivity where activityId = ${id}`))
    },
    async getActivityList(input, showNotAvailable = false) {
        let re = await dataBaseMethods.select(`select id, title, subTitle , imageUrl, price, category1, category2, updatedAt, startRegistration, endRegistration, startTime, endTime, maxParticipants, location from activities where title like '%${input}%'`)
        for (let i = 0; i < re.length; i++) {
            re[i] = await this.formatActivity(re[i])
            if (re[i].imageUrl.length) {
                re[i].imageUrl = [re[i].imageUrl[0]]
            } else {
                delete re[i].imageUrl
            }
        }
        re.sort((a, b) => {
            return a.joinedUserCount - b.joinedUserCount
        })
        let cate1 = []
        for (let i = 0; i < re.length; i++) {
            cate1.push(re[i].category1)
        }
        cate1 = [...new Set(cate1)]
        for (let i = 0; i < cate1.length; i++) {
            let currentCate1Name = cate1[i]
            let cate2 = []
            for (let i1 = 0; i1 < re.length; i1++) {
                if (re[i1].category1 == currentCate1Name) {
                    cate2.push(re[i1].category2)
                }
            }
            cate2 = [...new Set(cate2)]
            for (let i1 = 0; i1 < cate2.length; i1++) {
                let currentCate2Name = cate2[i1]
                let pros = []
                for (let i2 = 0; i2 < re.length; i2++) {
                    if (re[i2].category1 == currentCate1Name && re[i2].category2 == currentCate2Name) {
                        pros.push(re[i2])
                    }
                }
                cate2[i1] = {
                    title: currentCate2Name,
                    children: JSON.parse(JSON.stringify(pros))
                }
            }
            cate1[i] = {
                title: currentCate1Name,
                children: JSON.parse(JSON.stringify(cate2))
            }
        }
        return JSON.parse(JSON.stringify(cate1))
    },
    async getOneActivityInfo(id) {
        let re = await dataBaseMethods.select(`select * from activities where id = ${id}`)
        if (re.length) {
            return await this.formatActivity(re[0])
        } else {
            return undefined
        }
    },
    async updateInfo(item) {
        let id = item.id
        delete item.id
        delete item.createdAt
        delete item.updatedAt
        await dataBaseMethods.updateById('activities', id, item)
    },
    async add(item) {
        await dataBaseMethods.insertInto('activities', item)
    }
}

//购物车方法
const cart = {
    format(item) {
        try {
            item.createdAt = new Date(item.createdAt)
        } catch (error) {
            delete item.createdAt
        }
        try {
            item.updatedAt = new Date(item.updatedAt)
        } catch (error) {
            delete item.updatedAt
        }
        try {
            // item.options = JSON.parse(item.options)
            let t = ''
            for (let i = 0; i < Object.keys(item.options).length; i++) {
                let tt = Object.keys(item.options)[i]
                if (tt == 'name') tt = '姓名'
                if (tt == 'wechat') tt = '微信号'
                t += `${tt}:${item.options[Object.keys(item.options)[i]]}; `
            }
            item.optionsDisplay = t
        } catch (error) {
            item.options = {}
        }
        {
            item.price = item.info.price
            item.priceDisplay = (item.price / 100).toString() + ((item.price / 100).toString().split('.')[1] ? ((item.price / 100).toString().split('.')[1].length == 1 ? '0' : '') : '')
            item.priceDisplayTotal = (item.price * item.num / 100).toString() + ((item.price * item.num / 100).toString().split('.')[1] ? ((item.price * item.num / 100).toString().split('.')[1].length == 1 ? '0' : '') : '')

            item.priceWithFloat = tool.getSinglePrice(item.info, item.options, 1)
            item.priceWithFloatDisplay = (item.priceWithFloat / 100).toString() + ((item.priceWithFloat / 100).toString().split('.')[1] ? ((item.priceWithFloat / 100).toString().split('.')[1].length == 1 ? '0' : '') : '')
            item.priceWithFloatDisplayTotal = (item.priceWithFloat * item.num / 100).toString() + ((item.priceWithFloat * item.num / 100).toString().split('.')[1] ? ((item.priceWithFloat * item.num / 100).toString().split('.')[1].length == 1 ? '0' : '') : '')
        }
        return item
    },
    async getUserCartList(openId = undefined) {
        if (openId == undefined) return []
        let re = await dataBaseMethods.select(`select * from cart where openId = '${openId}'`)
        for (let i = 0; i < re.length; i++) {
            if (re[i].isPro) {
                re[i].info = await product.getOneProductInfo(re[i].itemId)
            } else {
                re[i].info = await activity.getOneActivityInfo(re[i].itemId)
            }
            re[i] = this.format(re[i])
        }
        re.sort((a, b) => {
            return a.createdAt - b.createdAt
        })
        return re
    },
    async addToCart(openId, isPro, itemId, num, price, options) {
        await dataBaseMethods.insertInto('cart', {
            openId,
            isPro,
            itemId,
            num,
            price,
            options
        })
    },
    async deleteFromCart(id) {
        await dataBaseMethods.deleteById('cart', id)
    },
    async updateNum(id, num) {
        await dataBaseMethods.updateById('cart', id, {
            num
        })
    },
    async deleteAll(openId) {
        await dataBaseMethods.exe(`delete from cart where openId = '${openId}'`)
    }
}

//订单方法
const order = {
    async createOrderNo(openId) {
        let re = await user.getSingleInfobyOpenId(openId)
        if (re === undefined) throwNewError('用户不存在')
        return `ORD${tool.formatDate2(new Date())}${re.id}`
    }
}

//定义接口
const ports = {
    test: {
        exp: '测试接口',
        method: 'get',
        async func(data) {
            return true
        }
    },
    setting: {
        banner: {
            exp: '轮播图',
            method: 'get',
            async func(data) {
                return await settings.getItem('banner')
            }
        },
        home: {
            exp: '首页',
            method: 'get',
            async func(data) {
                return {
                    notification: await settings.getItem('notification'),
                    banner: await settings.getItem('banner')
                }
            }
        },
        update: {
            exp: '更新设置',
            method: 'post',
            auth: {
                admin: true
            },
            async func(data) {
                await settings.setItem(data.param.key, data.param.value)
            }
        }
    },
    log: {
        exp: '输出',
        method: 'post',
        async func(data) {
            console.log(data.param)
        }
    },
    user: {
        search: {
            exp: '搜索用户',
            method: 'get',
            auth: {
                admin: true
            },
            async func(data) {
                return await user.searchUser(data.query.search, parseInt(data.query.page), parseInt(data.query.size))
            }
        },
        login: {
            exp: '用户登录',
            method: 'post',
            async func(data) {
                data.param.openId = await user.getOpenId(data.param.code)
                let re = await user.getSingleInfobyOpenId(data.param.openId)
                if (!re) {
                    if (data.param.userInfo === undefined) {
                        throwNewError('用户不存在且未提供用于注册的用户信息')
                    } else {
                        data.param.userInfo.openId = data.param.openId
                        await user.addUser(data.param.userInfo)
                    }
                }
                await user.updateUserToken(data.param.openId)
                re = await user.getSingleInfobyOpenId(data.param.openId)
                return re
            }
        },
        info: {
            exp: '获取用户信息',
            method: 'post',
            async func(data) {
                return await user.getSingleInfobyOpenId(await user.getOpenId(data.param.code))
            }
        },
        update: {
            avatar: {
                exp: '更新头像',
                method: 'post',
                auth: true,
                async func(data) {
                    let re = await user.getSingleInfobyOpenId(data.param.openId)
                    await user.updateUserInfo(data.param.openId, {
                        avatar: data.param.fileName
                    })
                    await deleteFile(re.avatar)
                }
            },
            info: {
                exp: '基本信息',
                method: 'post',
                auth: true,
                async func(data) {
                    let openId = data.param.openId
                    delete data.param.openId
                    await user.updateUserInfo(openId, data.param)
                }
            }
        }
    },
    getCities: {
        exp: '获取省市列表',
        method: 'get',
        async func() {
            const list = require('./citys.json')
            return list.provinces
        }
    },
    file: {
        delete: {
            exp: '删除文件',
            method: 'post',
            auth: true,
            async func(data) {
                await deleteFile(data.param.fileName)
            }
        }
    },
    getServerTime: {
        exp: '获取服务器时间',
        method: 'get',
        auth: true,
        async func() {
            return (new Date()).toString()
        }
    },
    product: {
        getList: {
            exp: '获取商品列表',
            method: 'get',
            async func(data) {
                return await product.getProductList(data.query.input ? data.query.input : '', data.query.sna == 'true')
            }
        },
        getSingleInfo: {
            exp: '获取单个商品信息',
            method: 'get',
            async func(data) {
                let re = product.getOneProductInfo(data.query.id)
                if (re) {
                    return re
                } else {
                    throwNewError('商品不存在')
                }

            }
        },
        updateInfo: {
            exp: '更新商品信息',
            method: 'post',
            auth: {
                admin: true
            },
            async func(data) {
                if (!data.param.item.id) {
                    await product.add(data.param.item)
                } else {
                    await product.updateInfo(data.param.item)
                }
            }
        },
        delete: {
            exp: '删除商品',
            method: 'post',
            auth: {
                admin: true,
            },
            async func(data) {
                let re = await product.getOneProductInfo(data.param.id)
                if (re) {
                    for (let i = 0; i < re.imageUrl.length; i++)await deleteFile(re.imageUrl[i])
                }
                await deleteFile(re.content)
                await dataBaseMethods.exe(`delete from cart where isPro = 1 and itemId = ${data.param.id}`)
                await dataBaseMethods.deleteById('products', data.param.id)
            }
        }
    },
    activity: {
        getList: {
            exp: '获取活动列表',
            method: 'get',
            async func(data) {
                return await activity.getActivityList(data.query.input ? data.query.input : '', data.query.sna == 'true')
            }
        },
        getSingleInfo: {
            exp: '获取单个活动信息',
            method: 'get',
            async func(data) {
                let re = activity.getOneActivityInfo(data.query.id)
                if (re) {
                    return re
                } else {
                    throwNewError('活动不存在')
                }

            }
        },
        updateInfo: {
            exp: '更新活动信息',
            method: 'post',
            auth: {
                admin: true
            },
            async func(data) {
                if (!data.param.item.id) {
                    await activity.add(data.param.item)
                } else {
                    await activity.updateInfo(data.param.item)
                }
            }
        },
        delete: {
            exp: '删除活动',
            method: 'post',
            auth: {
                admin: true,
            },
            async func(data) {
                let re = await activity.getOneActivityInfo(data.param.id)
                if (re) {
                    for (let i = 0; i < re.imageUrl.length; i++)await deleteFile(re.imageUrl[i])
                }
                await deleteFile(re.content)
                await dataBaseMethods.exe(`delete from cart where isPro = 0 and itemId = ${data.param.id}`)
                await dataBaseMethods.deleteById('activitys', data.param.id)
            }
        }
    },
    getTextFileContent: {
        exp: '获取文本文件内容',
        method: 'get',
        async func(data) {
            return await tool.getFileContent(data.query.fileName)
        }
    },
    tool: {
        getHTMLAttributeValues: {
            exp: '获取HTML字符串属性值',
            auth: {
                admin: true
            },
            method: 'post',
            async func(data) {
                return tool.getHTMLAttributes(data.param.htmlString, data.param.tagName, data.param.attributeName)
            }
        },
        updateHTMLAttributeValue: {
            exp: '替换HTML字符串属性值',
            auth: {
                admin: true
            },
            method: 'post',
            async func(data) {
                console.log(data.param)
                return tool.replaceHTMLAttributeValue(data.param.htmlString, data.param.attributeName, data.param.oldValue, data.param.newValue)
            }
        },
    },
    cart: {
        getList: {
            exp: '获取用户购物车列表',
            auth: true,
            method: 'get',
            async func(data) {
                let re = await cart.getUserCartList(data.query.openId)
                return re
            }
        },
        add: {
            exp: '添加购物车',
            auth: true,
            method: 'post',
            async func(data) {
                await cart.addToCart(data.param.openId, data.param.isPro, data.param.id, data.param.num, data.param.price, data.param.options)
            }
        },
        delete: {
            exp: '从购物车中删除',
            auth: true,
            method: 'post',
            async func(data) {
                await cart.deleteFromCart(data.param.id)
            }
        },
        update: {
            exp: '更新数量',
            auth: true,
            method: 'post',
            async func(data) {
                await cart.updateNum(data.param.id, data.param.num)
            }
        },
        deleteAll: {
            exp: '清空购物车',
            auth: true,
            method: 'post',
            async func(data) {
                await cart.deleteAll(data.param.openId)
            }
        }
    },
    coupons: {
        getList: {
            exp: '获取优惠券列表',
            method: 'get',
            auth: true,
            async func(data) {
                let re = await coupons.getList(data.query.openId)
                return re
            }
        },
        add: {
            exp: '添加优惠券',
            method: 'post',
            auth: {
                admin: true,
            },
            async func(data) {
                await coupons.add(data.param.openId, data.param.discount)
            }
        },
        delete: {
            exp: '删除优惠券',
            method: 'post',
            auth: {
                admin: true
            },
            async func(data) {
                await coupons.delete(data.param.id)
            }
        }
    },
    pay: {
        notify: {
            exp: '支付结果通知',
            method: 'post',
            async func(data) {
                console.log(data)
            }
        },
        getPrice: {
            exp: '计算商品价格',
            method: 'post',
            auth: true,
            async func(data) {
                let coupon = await coupons.getList(data.param.openId)
                let couponNumOnly = []
                for (let i = 0; i < coupon.length; i++)couponNumOnly.push(coupon[i].discount)
                let userData = await user.getSingleInfobyOpenId(data.param.openId)
                let vipStatus = {
                    pro: userData.vipStatus % 10,
                    act: parseInt(userData.vipStatus / 10)
                }
                if (data.param.set) {
                    let t = data.param.set
                    let ind = t.index
                    if (data.param.items[ind].useVip !== undefined) {
                        delete data.param.items[ind].useVip
                    }
                    if (data.param.items[ind].useCoupon !== undefined) {
                        delete data.param.items[ind].useCoupon
                    }
                    if (t.setVip !== undefined) {
                        if (!userData.isVIP) {
                            throw '需要开通PLUS会员'
                        }
                        data.param.items[ind].useVip = t.setVip
                    }
                    if (t.setCoupon !== undefined) {
                        data.param.items[ind].useCoupon = t.setCoupon
                    }
                }
                let items = data.param.items
                let used = {
                    coupons: [],
                    couponsSet: undefined,
                    vip: []
                }
                for (let i = 0; i < items.length; i++) {
                    items[i].info = items[i].isPro ? (await product.getOneProductInfo(items[i].itemId)) : (await activity.getOneActivityInfo(items[i].itemId))
                    if (items[i].useCoupon !== undefined) {
                        used.coupons.push(items[i].useCoupon)
                    }
                    if (items[i].useVip !== undefined) {
                        for (let i1 = 0; i1 < items[i].num; i1++) {
                            used.vip.push(items[i].useVip)
                        }
                    }
                }
                data.param.items = items
                used.couponsSet = [...new Set(used.coupons)]
                for (let i = 0; i < used.couponsSet.length; i++) {
                    if (tool.array.count(used.coupons, used.couponsSet[i]) > tool.array.count(couponNumOnly, used.couponsSet[i])) {
                        throw '优惠券已不足'
                    }
                }
                for (let i = 0; i < used.coupons.length; i++) {
                    coupon = tool.array.delete.byIndex(coupon, tool.array.getFirstIndex.byKeyValue(coupon, 'discount', used.coupons[i]))
                }
                if (tool.array.count(used.vip, 0) > vipStatus.act) {
                    throw 'PLUS会员活动权益已不足'
                } else {
                    vipStatus.act -= tool.array.count(used.vip, 0)
                }
                let priceTotalDisplay = 0
                for (let i = 0; i < data.param.items.length; i++) {
                    let current = tool.getSinglePrice(data.param.items[i].info, data.param.items[i].options, data.param.items[i].num)
                    if (data.param.items[i].isPro == 1 && data.param.items[i].useVip === 1) {
                        current /= data.param.items[i].num
                        let tc = 0
                        for (let i = 0; i < data.param.items[i].num; i++) {
                            if (vipStatus.pro) {
                                vipStatus.pro--
                            } else {
                                tc += current * 0.85
                            }
                        }
                        current = tc
                    }
                    if (data.param.items[i].isPro == 0 && data.param.items[i].useVip === 0) {
                        current = 0
                    }
                    if (data.param.items[i].useCoupon !== undefined) {
                        current *= data.param.items[i].useCoupon / 100
                    }
                    priceTotalDisplay += current
                }

                return {
                    coupons: coupon,
                    items: data.param.items,
                    vipStatus,
                    priceTotal: priceTotalDisplay,
                    priceTotalDisplay: (priceTotalDisplay / 100).toString() + ((priceTotalDisplay / 100).toString().split('.')[1] ? ((priceTotalDisplay / 100).toString().split('.')[1].length == 1 ? '0' : '') : '')
                }
            }
        },
        createOrder: {
            exp: '创建预支付订单',
            method: 'post',
            auth: true,
            async func(data, headers) {
                if (!data.param.code) throwNewError('code错误')
                let openId
                try {
                    openId = await user.getOpenId(data.param.code)
                } catch (error) {
                    throwNewError('code不存在或已失效')
                    return
                }
                let orderId = await order.createOrderNo(openId)
                return new Promise((resolve, reject) => {
                    const wxpay = new WXPAY({
                        appid: 'wx110f4e246caa7808',
                        mch_id: '1700332805',
                        partner_key: 'czoNCvZMqhwu6vermWxRus6WZXdNc9FN',
                        // pfx: 'path-to-your-pfx-cert.pfx'
                    });
                    const order = {
                        body: data.param.desc,
                        out_trade_no: orderId,
                        total_fee: data.param.price,  // 订单金额（分）
                        spbill_create_ip: headers.host['x-real-ip'],  // 用户IP
                        notify_url: 'https://coffeeshopapi.zhangtianyu.net/api/pay/notify',  // 支付回调地址
                        trade_type: 'JSAPI',  // 小程序支付
                        openid: openId  // 用户openid
                    };
                    wxpay.createOrder(order).then((result) => {
                        console.log('支付订单创建成功:', result);
                        // 可以返回prepay_id等信息给前端发起支付
                    }).catch((err) => {
                        console.error('支付订单创建失败:', err);
                    });
                })
            }
        }
    }
}